package ar.edu.utn.frgp.servicio;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import ar.edu.utn.frgp.model.ReservaHecha;
import com.mysql.jdbc.Connection;
import java.sql.PreparedStatement;

public class ReservaHechaService {

	public List<ReservaHecha> getReservasHechas() throws Exception{
		List<ReservaHecha> res = new ArrayList<ReservaHecha>();
		
		String host = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String pass = "admin";
		String dbName = "tpc1_Grupo6";
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = (Connection) DriverManager.getConnection(host + dbName, user, pass);
			
			try{
				java.sql.Statement st = conn.createStatement();		
				ResultSet rs = st.executeQuery("select r.idReserva, c.nombre, c.apellido, p.ciudadOrigen origen, p.ciudadaDestino destino, p.fechaDesde fchDesde, p.fechaHasta fchHasta " +
											"		from reservas r, " +
											"			 paquetes p, " +
											"			 clientes c  " +
											"	where c.idCliente = r.idCliente " +
											"	  and p.idPaquete = r.idPaquete  " +
											"	order by r.idReserva");
				while(rs.next()){
					res.add(new ReservaHecha(rs.getInt("idReserva"), rs.getString("nombre"), rs.getString("apellido"), rs.getString("origen"), rs.getString("destino"), rs.getString("fchDesde"), rs.getString("fchHasta")));
				}
			}catch(Exception e){
				e.printStackTrace();
			}
		} catch (Exception e) {
			throw new Exception(e.toString());
		}
		
		return res;
	}
	
	public void eliminarReserva(String idReserva)throws Exception{
		String host = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String pass = "admin";
		String dbName = "tpc1_Grupo6";
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conn = (Connection) DriverManager.getConnection(host + dbName, user, pass);
			java.sql.Statement st = conn.createStatement();
			st.executeUpdate("DELETE FROM reservas WHERE idReserva =  '" + idReserva + "' ");
			
		} catch (Exception e) {
			throw new Exception(e.toString());		
		}	
	}
	
	public String ObternerPaquete(String id)throws Exception{
		String host = "jdbc:mysql://localhost:3306/";
		String user = "root";
		String pass = "admin";
		String dbName = "tpc1_Grupo6";
		String paq = "";
		
		try {
			Class.forName("com.mysql.jdbc.Driver");				
			Connection conn = (Connection) DriverManager.getConnection(host + dbName, user, pass);
			java.sql.Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery("select idPaquete from reservas where idReserva =  '" + id + "'  ");
			while(rs.next()){
				paq = rs.getString("idPaquete");
			}
		} catch (Exception e) {
			throw new Exception(e.toString());
		}
		return paq;
	}
}
